/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Pojo.ChiTietHoaDon;
import Pojo.ChiTietHoaDonId;
import Pojo.HoaDon;
import Util.HibernateUtil;
import java.text.ParseException;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

/**
 *
 * @author HDKhanh
 */
public class DonHangDAO {
    
    private Session se;
    
    public DonHangDAO(){
        SessionFactory sf = HibernateUtil.getSessionFactory();
        se = sf.openSession();
    }
    public List<Object> View() throws ParseException{
        List list = null;
        se = HibernateUtil.getSessionFactory().openSession();
        Transaction tr = null;
        try
        {
            tr = se.beginTransaction();
            String sql = "select hd.mahd as mahd, hd.nguoiDung.taikhoan as taikhoan, hd.ngaylap as ngaylap, count(ct.sanPham) as  SL, sum(ct.dongia*ct.soluong) as  total\n" +
                        "from HoaDon as hd\n" +
                        "join hd.chiTietHoaDons as ct\n" +
                        "GROUP BY hd.mahd,hd.ngaylap, hd.nguoiDung";
            list = se.createQuery(sql).list(); 
            se.flush();
            tr.commit();
        }
        catch (Exception e){
            if(se.getTransaction().isActive())
                se.getTransaction().rollback();
            if (tr!=null) tr.rollback();
            System.out.println(e.getMessage());
        }
        finally
        {
            se.close();
        }
        
        return list;
    }
    
    public List<Object> Search(String taikhoan) throws ParseException{
        List list = null;
        se = HibernateUtil.getSessionFactory().openSession();
        Transaction tr = null;
        try
        {
            tr = se.beginTransaction();
            String sql = "select hd.mahd as mahd, hd.nguoiDung.taikhoan as taikhoan, hd.ngaylap as ngaylap, count(ct.sanPham) as  SL, sum(ct.dongia*ct.soluong) as  total\n" +
                        "from HoaDon as hd\n" +
                        "join hd.chiTietHoaDons as ct\n" +
                        "GROUP BY hd.mahd,hd.ngaylap, hd.nguoiDung\n" +
                        "having hd.nguoiDung.taikhoan like '%"+taikhoan+"%'";
            list = se.createQuery(sql).list(); 
            se.flush();
            tr.commit();
        }
        catch (Exception e){
            if(se.getTransaction().isActive())
                se.getTransaction().rollback();
            if (tr!=null) tr.rollback();
            System.out.println(e.getMessage());
        }
        finally
        {
            se.close();
        }
        
        return list;
    }
    
    public List<Object> ViewInfo(String mahd) throws ParseException{
        List list = null;
        se = HibernateUtil.getSessionFactory().openSession();
        Transaction tr = null;
        try
        {
            tr = se.beginTransaction();
            String sql = "select hd.mahd as mahd, hd.nguoiDung.tennguoidung as taikhoan, hd.nguoiDung.taikhoan, hd.ngaylap as ngaylap, ct.soluong as  SL, sum(ct.dongia*ct.soluong) as  total, sp.tensp, sp.hinhanh\n" +
                        "from HoaDon as hd\n" +
                        "join hd.chiTietHoaDons as ct\n" +
                        "join ct.sanPham as sp\n" +
                        "GROUP BY hd.mahd,hd.ngaylap, hd.nguoiDung.tennguoidung, hd.nguoiDung.taikhoan, sp.tensp, ct.soluong, sp.hinhanh\n" +
                        "having hd.mahd = "+mahd;
            list = se.createQuery(sql).list(); 
            se.flush();
            tr.commit();
        }
        catch (Exception e){
            if(se.getTransaction().isActive())
                se.getTransaction().rollback();
            if (tr!=null) tr.rollback();
            System.out.println(e.getMessage());
        }
        finally
        {
            se.close();
        }
        
        return list;
    }
    
    public List<Object> ViewInfoByUser(String taikhoan) throws ParseException{
        List list = null;
        se = HibernateUtil.getSessionFactory().openSession();
        Transaction tr = null;
        try
        {
            tr = se.beginTransaction();
            String sql = "from HoaDon as hd \n" +
                        "join hd.chiTietHoaDons as ct\n" +
                        "join ct.sanPham as sp \n" +
                        "join hd.nguoiDung as nd\n" +
                        "where hd.nguoiDung.taikhoan = '"+taikhoan+"'";
            list = se.createQuery(sql).list(); 
            se.flush();
            tr.commit();
        }
        catch (Exception e){
            if(se.getTransaction().isActive())
                se.getTransaction().rollback();
            if (tr!=null) tr.rollback();
            System.out.println(e.getMessage());
        }
        finally
        {
            se.close();
        }
        
        return list;
    }
    public boolean DeleteChiTietDonHang(String mahd, String masp)
    {
        se = HibernateUtil.getSessionFactory().openSession();
        Transaction tr = null;
        try
        {
            tr = se.beginTransaction();
//            String sql = "delete from ChiTietHoaDon where mahd= :mahd AND masp= :masp";
//            Query query = se.createQuery(sql);
//            query.setString("mahd", mahd);
//            query.setString("masp", masp);
//            //list = se.createQuery(sql).list();
            ChiTietHoaDonId ctID = new ChiTietHoaDonId( Integer.parseInt(masp),Integer.parseInt(mahd));
            ChiTietHoaDon ct = new ChiTietHoaDon();
            ct.setId(ctID);
            se.delete(ct);
            //se.flush();
            tr.commit();
        }
        catch (Exception e){
            if(se.getTransaction().isActive())
                se.getTransaction().rollback();
            if (tr!=null) tr.rollback();
            System.out.println(e.getMessage());
        }
        finally
        {
            se.close();
        }
        return false;
    }   
}
